package Models.dao;

import Models.enity.Transactional;
import Models.utils.DataSourceManager;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class TransactionalDao {
    private QueryRunner runner = new QueryRunner(DataSourceManager.getDataSource());

    public int insert(Transactional transactional){
        String sql = "INSERT INTO transactional(type_id,detail_id,insurance_level,isFirstCommit" +
                ",HandleDepartment,ResponseTime,SolveTime,note,description,applyPlace,applyPerson," +
                "telephone,status_id,level_id,cuiban_count,created_at,reportPlace)" +
                "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        try {
            return runner.update(sql,transactional.getType_id(),transactional.getDetail_id(),
                    transactional.getInsurance_level(), transactional.getIsFirstCommit(),
                    transactional.getHandleDepartment(), transactional.getResponseTime(),
                    transactional.getSolveTime(),transactional.getNote(),
                    transactional.getDescription(), transactional.getApplyPlace(),
                    transactional.getApplyPerson(),transactional.getTelephone(),
                    transactional.getStatus_id(),transactional.getLevel_id(),
                    transactional.getCuiban_count(),transactional.getCreated_at(),
                    transactional.getReportPlace());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    public Transactional findById(String id){
        String sql = "SELECT T.id,status.status,type.type,detail.detail,T.applyPlace,T.cuiban_count,T.created_at FROM transactional T " +
                "LEFT JOIN status ON T.status_id=status.id " +
                "LEFT JOIN type ON T.type_id=type.id " +
                "LEFT JOIN detail ON T.detail_id=detail.id WHERE T.id=?";
        try {
            return runner.query(sql,new BeanHandler<Transactional>(Transactional.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  null;
    }

    public List<Transactional> findCombineData(){
        String sql = "SELECT T.id,status.status,type.type,detail.detail,T.applyPlace,T.cuiban_count,T.created_at FROM transactional T " +
                "LEFT JOIN status ON T.status_id=status.id " +
                "LEFT JOIN type ON T.type_id=type.id " +
                "LEFT JOIN detail ON T.detail_id=detail.id";
        try {
            return  runner.query(sql,new BeanListHandler<Transactional>(Transactional.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int UpdateStatus(String id ,String status){
        String sql = "UPDATE transactional SET status_id=? WHERE id=?";
        try {
            return runner.update(sql,status,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }
    public List<Transactional> findCombineDataByStatus(String status){
        String sql = "SELECT T.id,status.status,type.type,detail.detail,T.applyPlace,T.cuiban_count,T.created_at FROM transactional T " +
                "LEFT JOIN status ON T.status_id=status.id " +
                "LEFT JOIN type ON T.type_id=type.id " +
                "LEFT JOIN detail ON T.detail_id=detail.id WHERE T.status_id=?";
        try {
            return runner.query(sql,new BeanListHandler<Transactional>(Transactional.class),status);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public int addCuiBan(String id){
        String sql = "UPDATE transactional SET cuiban_count=cuiban_count+1 WHERE id=?";
        try {
            return runner.update(sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    public List<Transactional> findAll(){
        String sql = "SELECT * FROM transactional";
        try {
            return runner.query(sql,new BeanListHandler<Transactional>(Transactional.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public long findDataSize(){
        String sql = "SELECT count(id) FROM transactional";

        try {
            return runner.query(sql,new ScalarHandler<Long>());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return new Long(-1);
    }

    public List<Transactional> findPaginationData(int skip,int pageSize){
        String sql = "SELECT T.created_at,T.applyPlace,type.type,detail.detail,T.cuiban_count FROM transactional T " +
                "LEFT JOIN type ON T.type_id=type.id " +
                "LEFT JOIN detail ON T.detail_id=detail.id limit ?,?";
        try {
            return runner.query(sql,new BeanListHandler<Transactional>(Transactional.class),skip,pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
